from django.db import models
from django.db.models.functions import Coalesce
from django.utils import timezone
from django.utils.functional import cached_property

# Create your models here.

# 盲盒商品管理器
class BlindBoxManager(models.Manager):
    """
    自定义盲盒管理器
    """
    def get_queryset(self):
        return super().get_queryset()
    
    def get_active_boxes(self):
        """获取所有上架的盲盒"""
        return self.filter(status=1)
    
    def get_by_market(self, market_id):
        """获取指定超市的盲盒"""
        return self.filter(market_id=market_id, status=1)
    
    def get_featured(self, limit=4):
        """获取推荐盲盒"""
        # 连接推荐表查询推荐盲盒
        from django.db import connection
        
        with connection.cursor() as cursor:
            # 先尝试从推荐表获取
            cursor.execute('''
                SELECT b.* 
                FROM tb_blind_box b
                JOIN tb_recommendation r ON b.box_id = r.box_id
                WHERE b.status = 1 
                  AND r.position = 'home_featured'
                  AND (r.end_time IS NULL OR r.end_time > NOW())
                ORDER BY r.sort_order ASC
                LIMIT %s
            ''', [limit])
            
            columns = [col[0] for col in cursor.description]
            result = [dict(zip(columns, row)) for row in cursor.fetchall()]
            
            # 如果推荐表没有数据，则返回销量最高的
            if not result:
                cursor.execute('''
                    SELECT b.* 
                    FROM tb_blind_box b 
                    WHERE b.status = 1 
                    ORDER BY b.sales DESC 
                    LIMIT %s
                ''', [limit])
                
                columns = [col[0] for col in cursor.description]
                result = [dict(zip(columns, row)) for row in cursor.fetchall()]
                
            return result

# 盲盒商品模型
class BlindBox(models.Model):
    """
    盲盒商品模型 (对应数据库tb_blind_box表)
    
    使用Django的ORM但不创建表，而是映射到现有表
    """
    box_id = models.BigAutoField(primary_key=True)
    market_id = models.BigIntegerField(null=True, blank=True)
    name = models.CharField(max_length=100)
    description = models.TextField(null=True, blank=True)
    original_price = models.DecimalField(max_digits=10, decimal_places=2, null=True, blank=True)
    discount_price = models.DecimalField(max_digits=10, decimal_places=2, null=True, blank=True)
    discount_rate = models.DecimalField(max_digits=3, decimal_places=2, null=True, blank=True)
    category = models.CharField(max_length=50, null=True, blank=True)
    stock = models.IntegerField(default=0)
    sales = models.IntegerField(default=0)
    carbon_footprint = models.CharField(max_length=20, null=True, blank=True)
    weekly_price = models.DecimalField(max_digits=10, decimal_places=2, null=True, blank=True)
    unique_code = models.CharField(max_length=20, null=True, blank=True)
    window_category_alert = models.BooleanField(default=False)
    create_time = models.DateTimeField(null=True, blank=True)
    expiry_date = models.DateField(null=True, blank=True)
    update_time = models.DateTimeField(null=True, blank=True)
    status = models.SmallIntegerField(default=1)
    image_url = models.CharField(max_length=255, null=True, blank=True)
    
    # 自定义管理器
    objects = BlindBoxManager()
    
    class Meta:
        managed = False  # 声明该模型不由Django管理表的创建和删除
        db_table = 'tb_blind_box'  # 指定映射到的数据库表名
        app_label = 'blindbox'
        verbose_name = '盲盒商品'
        verbose_name_plural = '盲盒商品'
        
    def __str__(self):
        return self.name
        
    @property
    def price(self):
        """返回当前价格，优先使用折扣价"""
        return self.discount_price if self.discount_price else self.original_price
        
    @property
    def has_discount(self):
        """检查是否有折扣"""
        return self.discount_price is not None and self.discount_price < self.original_price
        
    @property
    def discount_percentage(self):
        """计算折扣百分比"""
        if self.has_discount and self.original_price:
            return int((1 - self.discount_price / self.original_price) * 100)
        return 0
        
    def get_contents(self):
        """获取盲盒内容"""
        from django.db import connection
        
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT bc.content_id, bc.box_id, bc.product_id, bc.quantity, 
                       p.name, p.category, p.brand, p.weight, p.expiry_date, 
                       p.original_price, p.image_url
                FROM tb_box_content bc
                JOIN tb_product p ON bc.product_id = p.product_id
                WHERE bc.box_id = %s
            """, [self.box_id])
            
            columns = [col[0] for col in cursor.description]
            return [dict(zip(columns, row)) for row in cursor.fetchall()]
            
    def get_market(self):
        """获取所属超市信息"""
        from market.models import Supermarket
        return Supermarket.objects.filter(market_id=self.market_id).first()
            
    def get_reviews(self, limit=5):
        """获取商品评价"""
        return Review.objects.filter(box_id=self.box_id).order_by('-create_time')[:limit]

# 商品基础信息模型
class Product(models.Model):
    """
    商品基础信息模型 (对应数据库tb_product表)
    """
    product_id = models.BigAutoField(primary_key=True)
    name = models.CharField(max_length=100)
    category = models.CharField(max_length=50, null=True, blank=True)
    brand = models.CharField(max_length=50, null=True, blank=True)
    weight = models.CharField(max_length=20, null=True, blank=True)
    expiry_date = models.DateField(null=True, blank=True)
    original_price = models.DecimalField(max_digits=10, decimal_places=2, null=True, blank=True)
    image_url = models.CharField(max_length=255, null=True, blank=True)
    create_time = models.DateTimeField(auto_now_add=True, null=True, blank=True)
    update_time = models.DateTimeField(auto_now=True, null=True, blank=True)
    
    class Meta:
        managed = False
        db_table = 'tb_product'
        app_label = 'blindbox'
        verbose_name = '商品基础信息'
        verbose_name_plural = '商品基础信息'
        
    def __str__(self):
        return self.name

# 盲盒内容模型
class BoxContent(models.Model):
    """
    盲盒内容模型 (对应数据库tb_box_content表)
    """
    content_id = models.BigAutoField(primary_key=True)
    box_id = models.BigIntegerField(db_index=True)
    product_id = models.BigIntegerField(db_index=True)
    quantity = models.IntegerField(default=1)
    create_time = models.DateTimeField(auto_now_add=True, null=True, blank=True)
    
    # 关联，直接使用已经存在的字段进行关联，不指定db_column
    box_obj = models.ForeignKey(BlindBox, on_delete=models.CASCADE, related_name='contents', to_field='box_id')
    product_obj = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='box_contents', to_field='product_id')
    
    class Meta:
        managed = False
        db_table = 'tb_box_content'
        app_label = 'blindbox'
        verbose_name = '盲盒内容'
        verbose_name_plural = '盲盒内容'

# 评价表
class Review(models.Model):
    """
    评价模型 (对应数据库tb_review表)
    """
    review_id = models.BigAutoField(primary_key=True)
    user_id = models.BigIntegerField(null=True, blank=True)
    box_id = models.BigIntegerField(null=True, blank=True)
    order_id = models.BigIntegerField(null=True, blank=True)
    star_rating = models.SmallIntegerField(default=5)
    content = models.TextField(null=True, blank=True)
    images = models.CharField(max_length=1000, null=True, blank=True)
    like_count = models.IntegerField(default=0)
    is_anonymous = models.BooleanField(default=False)
    create_time = models.DateTimeField(default=timezone.now)
    
    class Meta:
        managed = False
        db_table = 'tb_review'
        app_label = 'blindbox'
        verbose_name = '商品评价'
        verbose_name_plural = '商品评价'
    
    @cached_property
    def user(self):
        from django.contrib.auth.models import User
        return User.objects.filter(id=self.user_id).first()

# 推荐模型
class Recommendation(models.Model):
    """
    推荐模型 (对应数据库tb_recommendation表)
    """
    recommendation_id = models.BigAutoField(primary_key=True)
    box_id = models.BigIntegerField()
    position = models.CharField(max_length=50)
    sort_order = models.IntegerField(default=0)
    start_time = models.DateTimeField(null=True, blank=True)
    end_time = models.DateTimeField(null=True, blank=True)
    create_time = models.DateTimeField(auto_now_add=True)
    update_time = models.DateTimeField(auto_now=True)
    
    # 关联，直接使用已经存在的字段进行关联，不指定db_column
    box_obj = models.ForeignKey(BlindBox, on_delete=models.CASCADE, related_name='recommendations', to_field='box_id')
    
    class Meta:
        managed = False
        db_table = 'tb_recommendation'
        app_label = 'blindbox'
        verbose_name = '推荐设置'
        verbose_name_plural = '推荐设置'
